#! /bin/bash

#采集日期
if [ $# -eq 0 ]
then
    DATE=`date -d "-1 day" +%Y-%m-%d`
else
    DATE=$1
fi

YEARSTR=`date -d "${DATE}" +%Y`
MONTHSTR=`date -d "${DATE}" +%m`
DAYSTR=`date -d "${DATE}" +%d`



hive -S -e "

ALTER TABLE itcast_dws.customer_signup_dws DROP PARTITION(yearinfo='${YEARSTR}', monthinfo='-1', dayinfo='-1');

ALTER TABLE itcast_dws.customer_signup_dws DROP PARTITION(yearinfo='${YEARSTR}', monthinfo='${MONTHSTR}', dayinfo='-1');


--校区、学科组合分组
--小时
INSERT INTO itcast_dws.customer_signup_dws PARTITION(payment_time_year, payment_time_month, payment_time_day)
SELECT
    count(dwm.customer_id) as signup_num,
    '-1' origin_type,
    dwm.origin_type_state,
    dwm.itcast_school_id,
    dwm.itcast_school_name,
    dwm.itcast_subject_id,
    dwm.itcast_subject_name,
    -1 as tdepart_id,
    '-1' tdepart_name,
    dwm.payment_time_hour,
    '1' grouptype, 
    '1' as time_type,
    dwm.payment_time_year,
    dwm.payment_time_month,
    dwm.payment_time_day
from itcast_dwm.customer_signup_dwm dwm
where  CONCAT_WS('-',dwm.payment_time_year,dwm.payment_time_month,dwm.payment_time_day)=${DATE}
GROUP BY dwm.origin_type_state,
    dwm.payment_time_year, dwm.payment_time_month, dwm.payment_time_day, dwm.payment_time_hour,
    dwm.itcast_school_id, dwm.itcast_school_name, dwm.itcast_subject_id, dwm.itcast_subject_name;
--天
INSERT INTO itcast_dws.customer_signup_dws PARTITION(payment_time_year, payment_time_month, payment_time_day)
SELECT
    count(dwm.customer_id) as signup_num,
    '-1' origin_type,
    dwm.origin_type_state,
    dwm.itcast_school_id,
    dwm.itcast_school_name,
    dwm.itcast_subject_id,
    dwm.itcast_subject_name,
    -1 as tdepart_id,
    '-1' tdepart_name,
    '-1' payment_time_hour,
    '1' grouptype, 
    '2' as time_type,
    dwm.payment_time_year,
    dwm.payment_time_month,
    dwm.payment_time_day
from itcast_dwm.customer_signup_dwm dwm
where  CONCAT_WS('-',dwm.payment_time_year,dwm.payment_time_month,dwm.payment_time_day)=${DATE}
GROUP BY dwm.origin_type_state,
    dwm.payment_time_year, dwm.payment_time_month, dwm.payment_time_day, 
    dwm.itcast_school_id, dwm.itcast_school_name, dwm.itcast_subject_id, dwm.itcast_subject_name;
--月
INSERT INTO itcast_dws.customer_signup_dws PARTITION(payment_time_year, payment_time_month, payment_time_day)
SELECT
    count(dwm.customer_id) as signup_num,
    '-1' origin_type,
    dwm.origin_type_state,
    dwm.itcast_school_id,
    dwm.itcast_school_name,
    dwm.itcast_subject_id,
    dwm.itcast_subject_name,
    -1 as tdepart_id,
    '-1' tdepart_name,
    '-1' payment_time_hour,
    '1' grouptype, 
    '4' as time_type,
    dwm.payment_time_year,
    dwm.payment_time_month,
    '-1' payment_time_day
from itcast_dwm.customer_signup_dwm dwm
where  CONCAT_WS('-',dwm.payment_time_year,dwm.payment_time_month)=CONCAT_WS('-',${YEARSTR},${MONTHSTR})
GROUP BY dwm.origin_type_state,
    dwm.payment_time_year, dwm.payment_time_month, 
    dwm.itcast_school_id, dwm.itcast_school_name, dwm.itcast_subject_id, dwm.itcast_subject_name;
--年
INSERT INTO itcast_dws.customer_signup_dws PARTITION(payment_time_year, payment_time_month, payment_time_day)
SELECT
    count(dwm.customer_id) as signup_num,
    '-1' origin_type,
    dwm.origin_type_state,
    dwm.itcast_school_id,
    dwm.itcast_school_name,
    dwm.itcast_subject_id,
    dwm.itcast_subject_name,
    -1 as tdepart_id,
    '-1' tdepart_name,
    '-1' payment_time_hour,
    '1' grouptype, 
    '5' as time_type,
    dwm.payment_time_year,
    '-1' payment_time_month,
    '-1' payment_time_day
from itcast_dwm.customer_signup_dwm dwm
where dwm.payment_time_year=${YEARSTR}
GROUP BY dwm.origin_type_state,
    dwm.payment_time_year, 
    dwm.itcast_school_id, dwm.itcast_school_name, dwm.itcast_subject_id, dwm.itcast_subject_name;


--咨询中心分组
--小时
INSERT INTO itcast_dws.customer_signup_dws PARTITION(payment_time_year, payment_time_month, payment_time_day)
SELECT
    count(dwm.customer_id) as signup_num,
    '-1' origin_type,
    dwm.origin_type_state,
    '-1' itcast_school_id,
    '-1' itcast_school_name,
    '-1' itcast_subject_id,
    '-1' itcast_subject_name,
    dwm.tdepart_id,
    dwm.tdepart_name,
    dwm.payment_time_hour,
    '3' grouptype, 
    '1' as time_type,
    dwm.payment_time_year,
    dwm.payment_time_month,
    dwm.payment_time_day
from itcast_dwm.customer_signup_dwm dwm
where  CONCAT_WS('-',dwm.payment_time_year,dwm.payment_time_month,dwm.payment_time_day)=${DATE}
GROUP BY dwm.origin_type_state,
    dwm.payment_time_year, dwm.payment_time_month, dwm.payment_time_day, dwm.payment_time_hour,
    dwm.tdepart_id, dwm.tdepart_name;
--天
INSERT INTO itcast_dws.customer_signup_dws PARTITION(payment_time_year, payment_time_month, payment_time_day)
SELECT
    count(dwm.customer_id) as signup_num,
    '-1' origin_type,
    dwm.origin_type_state,
    '-1' itcast_school_id,
    '-1' itcast_school_name,
    '-1' itcast_subject_id,
    '-1' itcast_subject_name,
    dwm.tdepart_id,
    dwm.tdepart_name,
    '-1' payment_time_hour,
    '3' grouptype, 
    '2' as time_type,
    dwm.payment_time_year,
    dwm.payment_time_month,
    dwm.payment_time_day
from itcast_dwm.customer_signup_dwm dwm
where  CONCAT_WS('-',dwm.payment_time_year,dwm.payment_time_month,dwm.payment_time_day)=${DATE}
GROUP BY dwm.origin_type_state,
    dwm.payment_time_year, dwm.payment_time_month, dwm.payment_time_day,
    dwm.tdepart_id, dwm.tdepart_name;
--月
INSERT INTO itcast_dws.customer_signup_dws PARTITION(payment_time_year, payment_time_month, payment_time_day)
SELECT
    count(dwm.customer_id) as signup_num,
    '-1' origin_type,
    dwm.origin_type_state,
    '-1' itcast_school_id,
    '-1' itcast_school_name,
    '-1' itcast_subject_id,
    '-1' itcast_subject_name,
    dwm.tdepart_id,
    dwm.tdepart_name,
    '-1' payment_time_hour,
    '3' grouptype, 
    '4' as time_type,
    dwm.payment_time_year,
    dwm.payment_time_month,
    '-1' payment_time_day
from itcast_dwm.customer_signup_dwm dwm
where  CONCAT_WS('-',dwm.payment_time_year,dwm.payment_time_month)=CONCAT_WS('-',${YEARSTR},${MONTHSTR})
GROUP BY dwm.origin_type_state,
    dwm.payment_time_year, dwm.payment_time_month,
    dwm.tdepart_id, dwm.tdepart_name;
--年
INSERT INTO itcast_dws.customer_signup_dws PARTITION(payment_time_year, payment_time_month, payment_time_day)
SELECT
    count(dwm.customer_id) as signup_num,
    '-1' origin_type,
    dwm.origin_type_state,
    '-1' itcast_school_id,
    '-1' itcast_school_name,
    '-1' itcast_subject_id,
    '-1' itcast_subject_name,
    dwm.tdepart_id,
    dwm.tdepart_name,
    '-1' payment_time_hour,
    '3' grouptype, 
    '5' as time_type,
    dwm.payment_time_year,
    '-1' payment_time_month,
    '-1' payment_time_day
from itcast_dwm.customer_signup_dwm dwm
where dwm.payment_time_year=${YEARSTR}
GROUP BY dwm.origin_type_state,
    dwm.payment_time_year,
    dwm.tdepart_id, dwm.tdepart_name;

--来源渠道分组
--小时
INSERT INTO itcast_dws.customer_signup_dws PARTITION(payment_time_year, payment_time_month, payment_time_day)
SELECT
    count(dwm.customer_id) as signup_num,
    dwm.origin_type,
    dwm.origin_type_state,
    '-1' itcast_school_id,
    '-1' itcast_school_name,
    '-1' itcast_subject_id,
    '-1' itcast_subject_name,
    '-1' as tdepart_id,
    '-1' tdepart_name,
    dwm.payment_time_hour,
    '2' grouptype, 
    '1' as time_type,
    dwm.payment_time_year,
    dwm.payment_time_month,
    dwm.payment_time_day
from itcast_dwm.customer_signup_dwm dwm
where  CONCAT_WS('-',dwm.payment_time_year,dwm.payment_time_month,dwm.payment_time_day)=${DATE}
GROUP BY dwm.origin_type_state,
    dwm.payment_time_year, dwm.payment_time_month, dwm.payment_time_day, dwm.payment_time_hour,
    dwm.origin_type;
--天
INSERT INTO itcast_dws.customer_signup_dws PARTITION(payment_time_year, payment_time_month, payment_time_day)
SELECT
    count(dwm.customer_id) as signup_num,
    dwm.origin_type,
    dwm.origin_type_state,
    '-1' itcast_school_id,
    '-1' itcast_school_name,
    '-1' itcast_subject_id,
    '-1' itcast_subject_name,
    '-1' as tdepart_id,
    '-1' tdepart_name,
    '-1' payment_time_hour,
    '2' grouptype, 
    '2' as time_type,
    dwm.payment_time_year,
    dwm.payment_time_month,
    dwm.payment_time_day
from itcast_dwm.customer_signup_dwm dwm
where  CONCAT_WS('-',dwm.payment_time_year,dwm.payment_time_month,dwm.payment_time_day)=${DATE}
GROUP BY dwm.origin_type_state,
    dwm.payment_time_year, dwm.payment_time_month, dwm.payment_time_day,
    dwm.origin_type;
--月
INSERT INTO itcast_dws.customer_signup_dws PARTITION(payment_time_year, payment_time_month, payment_time_day)
SELECT
    count(dwm.customer_id) as signup_num,
    dwm.origin_type,
    dwm.origin_type_state,
    '-1' itcast_school_id,
    '-1' itcast_school_name,
    '-1' itcast_subject_id,
    '-1' itcast_subject_name,
    '-1' as tdepart_id,
    '-1' tdepart_name,
    '-1' payment_time_hour,
    '2' grouptype, 
    '4' as time_type,
    dwm.payment_time_year,
    dwm.payment_time_month,
    '-1' payment_time_day
from itcast_dwm.customer_signup_dwm dwm
where  CONCAT_WS('-',dwm.payment_time_year,dwm.payment_time_month)=CONCAT_WS('-',${YEARSTR},${MONTHSTR})
GROUP BY dwm.origin_type_state,
    dwm.payment_time_year, dwm.payment_time_month, 
    dwm.origin_type;
--年
INSERT INTO itcast_dws.customer_signup_dws PARTITION(payment_time_year, payment_time_month, payment_time_day)
SELECT
    count(dwm.customer_id) as signup_num,
    dwm.origin_type,
    dwm.origin_type_state,
    '-1' itcast_school_id,
    '-1' itcast_school_name,
    '-1' itcast_subject_id,
    '-1' itcast_subject_name,
    '-1' as tdepart_id,
    '-1' tdepart_name,
    '-1' payment_time_hour,
    '2' grouptype, 
    '5' as time_type,
    dwm.payment_time_year,
    '-1' payment_time_month,
    '-1' payment_time_day
from itcast_dwm.customer_signup_dwm dwm
where dwm.payment_time_year=${YEARSTR}
GROUP BY dwm.origin_type_state,
    dwm.payment_time_year, 
    dwm.origin_type;

--总数分组
--小时
INSERT INTO itcast_dws.customer_signup_dws PARTITION(payment_time_year, payment_time_month, payment_time_day)
SELECT
    count(dwm.customer_id) as signup_num,
    '-1' origin_type,
    dwm.origin_type_state,
    '-1' itcast_school_id,
    '-1' itcast_school_name,
    '-1' itcast_subject_id,
    '-1' itcast_subject_name,
    -1 as tdepart_id,
    '-1' tdepart_name,
    dwm.payment_time_hour,
    '4' grouptype, 
    '1' as time_type,
    dwm.payment_time_year,
    dwm.payment_time_month,
    dwm.payment_time_day
from itcast_dwm.customer_signup_dwm dwm
where  CONCAT_WS('-',dwm.payment_time_year,dwm.payment_time_month,dwm.payment_time_day)=${DATE}
GROUP BY dwm.origin_type_state,
    dwm.payment_time_year, dwm.payment_time_month, dwm.payment_time_day, dwm.payment_time_hour;
--天
INSERT INTO itcast_dws.customer_signup_dws PARTITION(payment_time_year, payment_time_month, payment_time_day)
SELECT
    count(dwm.customer_id) as signup_num,
    '-1' origin_type,
    dwm.origin_type_state,
    '-1' itcast_school_id,
    '-1' itcast_school_name,
    '-1' itcast_subject_id,
    '-1' itcast_subject_name,
    -1 as tdepart_id,
    '-1' tdepart_name,
    '-1' payment_time_hour,
    '4' grouptype, 
    '2' as time_type,
    dwm.payment_time_year,
    dwm.payment_time_month,
    dwm.payment_time_day
from itcast_dwm.customer_signup_dwm dwm
where  CONCAT_WS('-',dwm.payment_time_year,dwm.payment_time_month,dwm.payment_time_day)=${DATE}
GROUP BY dwm.origin_type_state,
    dwm.payment_time_year, dwm.payment_time_month, dwm.payment_time_day;
--月
INSERT INTO itcast_dws.customer_signup_dws PARTITION(payment_time_year, payment_time_month, payment_time_day)
SELECT
    count(dwm.customer_id) as signup_num,
    '-1' origin_type,
    dwm.origin_type_state,
    '-1' itcast_school_id,
    '-1' itcast_school_name,
    '-1' itcast_subject_id,
    '-1' itcast_subject_name,
    -1 as tdepart_id,
    '-1' tdepart_name,
    '-1' payment_time_hour,
    '4' grouptype, 
    '4' as time_type,
    dwm.payment_time_year,
    dwm.payment_time_month,
    '-1' payment_time_day
from itcast_dwm.customer_signup_dwm dwm
where  CONCAT_WS('-',dwm.payment_time_year,dwm.payment_time_month)=CONCAT_WS('-',${YEARSTR},${MONTHSTR})
GROUP BY dwm.origin_type_state,
    dwm.payment_time_year, dwm.payment_time_month;
--年
INSERT INTO itcast_dws.customer_signup_dws PARTITION(payment_time_year, payment_time_month, payment_time_day)
SELECT
    count(dwm.customer_id) as signup_num,
    '-1' origin_type,
    dwm.origin_type_state,
    '-1' itcast_school_id,
    '-1' itcast_school_name,
    '-1' itcast_subject_id,
    '-1' itcast_subject_name,
    -1 as tdepart_id,
    '-1' tdepart_name,
    '-1' payment_time_hour,
    '4' grouptype, 
    '5' as time_type,
    dwm.payment_time_year,
    '-1' payment_time_month,
    '-1' payment_time_day
from itcast_dwm.customer_signup_dwm dwm
where dwm.payment_time_year=${YEARSTR}
GROUP BY dwm.origin_type_state,
    dwm.payment_time_year;"